#!/usr/bin/env python
# -*- coding:utf-8 -*-
import configparser
import os
import sys

__dir__ = os.path.dirname(os.path.abspath(__file__))
sys.path.append(__dir__)
sys.path.append(os.path.abspath(os.path.join(__dir__, '../../..')))
f = r'E:\zentao_notify\vpTransdoor\setting.ini'
config_file = configparser.RawConfigParser()
config_file.read(f, encoding='UTF8')

obj_col = []
# 数据列方法名称
top_col = [
    "Premium_Fin", "Loan_amount", "First_Extral_Withdraw_ANB", "Repay_ANB", "Saving_Payment_Term",
    "Saving_Sum_Assured_USD", 'SA', "1Y_discount", "3Y_discount", "5Y_discount", "8Y_discount", "12Y_discount",
    "EGA_discount", "discount_adjusted_premium", "discount_adjusted_SA", "Chi", "Exchange_rate", "Display_rate",
    "Underlying_currency", "Display_currenty", "EGA_payment_term_index", "EGA_currency_index", "EGA_sum_assured_index",
    "EGA_SA_factor", "EGA_annual_premium", "EGA_annual_premium_HKD", "Adjusted_EGA_CV_col", "Adjusted_EGA_G_CV_col",
    "Total_EGA_premium", "SA_minimum", "low_SA_message", "max_withdrawal_age", "surrender_value", "seed_value",
    "max_withdrawal"
]
# 数据列的excel坐标
top_col2 = [
    "B18", "B19", "B20", "B21", "B23", "B24", "B25", "B26", "B27", "B28", "B29", "B30", "B31", "B32", "B33", "B35",
    "B36", "B37", "B38", "B39", "B40", "B41", "B42", "B43", "B44", "B45", "B46", "B47", "B48", "B50", "B51", "B53",
    "B54", "B56", "B57"
]
# 数据名称
top_col3 = [
    "Premium Fin", "Loan amount", "First Extral Withdraw ANB", "Repay ANB", "Saving Payment Term",
    "Saving Sum Assured USD", 'SA', "1Y discount", "3Y discount", "5Y discount", "8Y discount", "12Y discount",
    "EGA discount", "discount adjusted premium", "discount adjusted SA", "Chi", "Exchange rate", "Display rate",
    "Underlying currency", "Display currenty", "EGA payment term index", "EGA currency index", "EGA sum assured index",
    "EGA SA factor", "EGA annual premium", "EGA annual premium HKD", "Adjusted EGA CV col", "Adjusted EGA G CV col",
    "Total EGA premium", "SA minimum", "low SA message", "max withdrawal age", "surrender value", "seed value",
    "max withdrawal"
]
# 数据使用的EXCEL公式
top_col4 = [
    "=AND(C11>0,C6=0)", "=C44*C37*80%*C10", "=MIN(101,M162)", "0", "", "",
    "=VLOOKUP('SA',data3!A2:IG102,C42,FALSE)*(C5/C37/VLOOKUP('ANB',data3!A2:IG102,C42,FALSE))", "0",
    "=IF(C25/C36>=150000,10.4,IF(C25/C36>=100000,8,IF(C25/C36>=50000,6.8,IF(C25/C36>=35000,5.8,IF(C25/C36>=20000,3.8,0)))))-10.4",
    "=IF(C25/C36>=150000,6.4,IF(C25/C36>=100000,5,IF(C25/C36>=50000,4.2,IF(C25/C36>=35000,3.6,IF(C25/C36>=20000,2.4,0)))))-6.4",
    "=IF(C25/C36>=150000,4,IF(C25/C36>=100000,3.2,IF(C25/C36>=50000,2.6,IF(C25/C36>=35000,2.2,IF(C25/C36>=20000,1.6,0)))))-4",
    "=IF(C25/C36>=150000,3,IF(C25/C36>=100000,2.4,IF(C25/C36>=50000,2,IF(C25/C36>=35000,1.6,IF(C25/C36>=20000,1,0)))))-3",
    "=IF(C23=1,C26,IF(C23=3,C27,IF(C23=5,C28,IF(C23=8,C29,C30))))",
    "=(VLOOKUP('ANB',data3!A2:IG102,C42,FALSE)*1000/VLOOKUP('SA',data3!A2:IG102,C42,FALSE)-C31)*C25/1000",
    "=ROUNDUP(C5/(C32*C37)*C25/100,0)*100", "=IF(LOWER(B165)='chi','Yes','No')", "=IF(LOWER(C8)='hkd',8,1)",
    "=IF(LOWER(C8)='rmb-u',Notes!B2,IF(LOWER(C8)='hkd-u',7.8,1))",
    "=IF(LOWER(C8)='rmb-u','(USD)',IF(LOWER(C8)='hkd-u','(USD)',IF(LOWER(C8)='hkd','(HKD)',IF(LOWER(C8)='usd','(USD)'))))",
    "=IF(LOWER(C8)='rmb-u','(RMB)',IF(LOWER(C8)='hkd-u','(HKD)',IF(LOWER(C8)='hkd','(HKD)',IF(LOWER(C8)='usd','(USD)'))))",
    "=IF(C23=1,2,IF(C23=3,4,IF(C23=5,6,IF(C23=8,8,10))))", "=IF(C36=1,C40+1,C40+0)", "241",
    "=C24/VLOOKUP('SA',data3!A2:IG102,C42,FALSE)",
    "=(VLOOKUP('ANB',data3!A2:IG102,C42,FALSE)*1000/VLOOKUP('SA',data3!A2:IG102,C42,FALSE)-C31)*C24/1000",
    "=IF(C36=1,C44*7.8,C44)", "=COLUMN(AJ60)-COLUMN(B60)+1",
    "=COLUMN(AQ60)-COLUMN(B60)+1", "=SUM(AA62:AA160)", "=IF(C36=1,10000,80000)", "Notional Amount Too Low",
    "=IF(F8=0,F3,F5+F8-1)", "=VLOOKUP(C53,B62:AP162,COLUMN(AP162)-COLUMN(B62)+1,FALSE)", "=C54/(IF(F8=0,F3-F5+1,F8))",
    "=ROUNDDOWN(EA54*C37/1000,0)*1000"
]
top_col5 = [
    "FALSE", "TRUE", "TRUE", "TRUE", "TRUE", "TRUE", "TRUE", "TRUE", "TRUE", "TRUE", "TRUE", "TRUE", "TRUE", "TRUE",
    "TRUE", "TRUE", "TRUE", "FALSE", "TRUE", "TRUE", "FALSE", "FALSE", "TRUE", "TRUE", "TRUE", "TRUE", "TRUE", "TRUE",
    "TRUE", "TRUE", "TRUE", "TRUE", "TRUE", "FALSE", "TRUE", "TRUE", "TRUE", "TRUE", "TRUE", "TRUE"
]

std_col = [
    "Premium_Fin", "Loan_amount", "First_Extral_Withdraw_ANB", "Repay_ANB", "Saving_Payment_Term",
    "Saving_Sum_Assured_USD", 'SA', "1Y_discount", "3Y_discount", "5Y_discount", "8Y_discount", "12Y_discount",
    "EGA_discount", "discount_adjusted_premium", "discount_adjusted_SA", "Chi", "Exchange_rate", "Display_rate",
    "Underlying_currency", "Display_currenty", "EGA_payment_term_index", "EGA_currency_index", "EGA_sum_assured_index",
    "EGA_SA_factor", "EGA_annual_premium", "EGA_annual_premium_HKD", "Adjusted_EGA_CV_col", "Adjusted_EGA_G_CV_col",
    "Total_EGA_premium", "SA_minimum", "low_SA_message", "max_withdrawal_age", "surrender_value", "seed_value",
    "max_withdrawal"
]
# 数据列的excel坐标
std_col2 = [
    "B18", "B19", "B20", "B21", "B23", "B24", "B25", "B26", "B27", "B28", "B29", "B30", "B31", "B32", "B33", "B35",
    "B36", "B37", "B38", "B39", "B40", "B41", "B42", "B43", "B44", "B45", "B46", "B47", "B48", "B50", "B51", "B53",
    "B54", "B56", "B57"
]
# 数据名称
std_col3 = [
    "Premium Fin", "Loan amount", "First Extral Withdraw ANB", "Repay ANB", "Saving Payment Term",
    "Saving Sum Assured USD", 'SA', "1Y discount", "3Y discount", "5Y discount", "8Y discount", "12Y discount",
    "EGA discount", "discount adjusted premium", "discount adjusted SA", "Chi", "Exchange rate", "Display rate",
    "Underlying currency", "Display currenty", "EGA payment term index", "EGA currency index", "EGA sum assured index",
    "EGA SA factor", "EGA annual premium", "EGA annual premium HKD", "Adjusted EGA CV col", "Adjusted EGA G CV col",
    "Total EGA premium", "SA minimum", "low SA message", "max withdrawal age", "surrender value", "seed value",
    "max withdrawal"
]
# 数据使用的EXCEL公式
std_col4 = [
    "=AND(C11>0,C6=0)", "=C44*C37*80%*C10", "=MIN(101,M162)", "0", "", "",
    "=VLOOKUP('SA',data3!A2:IG102,C42,FALSE)*(C5/C37/VLOOKUP('ANB',data3!A2:IG102,C42,FALSE))", "0",
    "=IF(C25/C36>=150000,10.4,IF(C25/C36>=100000,8,IF(C25/C36>=50000,6.8,IF(C25/C36>=35000,5.8,IF(C25/C36>=20000,3.8,0)))))-10.4",
    "=IF(C25/C36>=150000,6.4,IF(C25/C36>=100000,5,IF(C25/C36>=50000,4.2,IF(C25/C36>=35000,3.6,IF(C25/C36>=20000,2.4,0)))))-6.4",
    "=IF(C25/C36>=150000,4,IF(C25/C36>=100000,3.2,IF(C25/C36>=50000,2.6,IF(C25/C36>=35000,2.2,IF(C25/C36>=20000,1.6,0)))))-4",
    "=IF(C25/C36>=150000,3,IF(C25/C36>=100000,2.4,IF(C25/C36>=50000,2,IF(C25/C36>=35000,1.6,IF(C25/C36>=20000,1,0)))))-3",
    "=IF(C23=1,C26,IF(C23=3,C27,IF(C23=5,C28,IF(C23=8,C29,C30))))",
    "=(VLOOKUP('ANB',data3!A2:IG102,C42,FALSE)*1000/VLOOKUP('SA',data3!A2:IG102,C42,FALSE)-C31)*C25/1000",
    "=ROUNDUP(C5/(C32*C37)*C25/100,0)*100", "=IF(LOWER(B165)='chi','Yes','No')", "=IF(LOWER(C8)='hkd',8,1)",
    "=IF(LOWER(C8)='rmb-u',Notes!B2,IF(LOWER(C8)='hkd-u',7.8,1))",
    "=IF(LOWER(C8)='rmb-u','(USD)',IF(LOWER(C8)='hkd-u','(USD)',IF(LOWER(C8)='hkd','(HKD)',IF(LOWER(C8)='usd','(USD)'))))",
    "=IF(LOWER(C8)='rmb-u','(RMB)',IF(LOWER(C8)='hkd-u','(HKD)',IF(LOWER(C8)='hkd','(HKD)',IF(LOWER(C8)='usd','(USD)'))))",
    "=IF(C23=1,2,IF(C23=3,4,IF(C23=5,6,IF(C23=8,8,10))))", "=IF(C36=1,C40+1,C40+0)", "241",
    "=C24/VLOOKUP('SA',data3!A2:IG102,C42,FALSE)",
    "=(VLOOKUP('ANB',data3!A2:IG102,C42,FALSE)*1000/VLOOKUP('SA',data3!A2:IG102,C42,FALSE)-C31)*C24/1000",
    "=IF(C36=1,C44*7.8,C44)", "=COLUMN(AJ60)-COLUMN(B60)+1",
    "=COLUMN(AQ60)-COLUMN(B60)+1", "=SUM(AA62:AA160)", "=IF(C36=1,10000,80000)", "Notional Amount Too Low",
    "=IF(F8=0,F3,F5+F8-1)", "=VLOOKUP(C53,B62:AP162,COLUMN(AP162)-COLUMN(B62)+1,FALSE)", "=C54/(IF(F8=0,F3-F5+1,F8))",
    "=ROUNDDOWN(EA54*C37/1000,0)*1000"
]
std_col5 = [
    "FALSE", "TRUE", "TRUE", "TRUE", "TRUE", "TRUE", "TRUE", "TRUE", "TRUE", "TRUE", "TRUE", "TRUE", "TRUE", "TRUE",
    "TRUE", "TRUE", "TRUE", "FALSE", "TRUE", "TRUE", "FALSE", "FALSE", "TRUE", "TRUE", "TRUE", "TRUE", "TRUE", "TRUE",
    "TRUE", "TRUE", "TRUE", "TRUE", "TRUE", "FALSE", "TRUE", "TRUE", "TRUE", "TRUE", "TRUE", "TRUE"
]

START = 13
END = 59
DECIMAL_NUM = 2
CALSS_NAME = "Ega"


def makeTabCalStd():
    arry_method = []
    arry_call = []
    try:
        p_path = config_file["excelobj"]["outPath"] + f"\\{str(CALSS_NAME).upper()}\\"
        str_out_file_path = p_path + CALSS_NAME + "TabCalStd.py"

        print("METHOD:" + "#" * 100)
        for _idx in range(len(std_col)):
            arry_method.append(
                " " * 4 + f"# 计算 {std_col3[_idx]} \n    # Params:\n    # {std_col3[_idx]} {std_col2[_idx]}: {std_col3[_idx]}")
            arry_method.append(" " * 4 + f"def get_{std_col[_idx]}(self,):")
            arry_method.append(" " * 8 + f"# 参考计算公式")
            arry_method.append(" " * 8 + f"# {std_col4[_idx]}")
            arry_method.append(" " * 8 + f"return 0")
            arry_method.append("\n")
        print("NAMING:" + "#" * 100)
        for _idx in range(END - START):
            if std_col5[_idx] == "TRUE":
                print(" " * 20 + f"one_{std_col[_idx]}_res = 0")
            else:
                print(" " * 20 + f"one_{std_col[_idx]}_res = ''")
        print("CALLING:" + "#" * 100)
        for _idx in range(END - START):
            arry_call.append(" " * 20 + f"# Col {_idx + START} 计算 {std_col3[_idx]}")
            arry_call.append(" " * 20 + f"one_{std_col[_idx]} = calStd.get_{std_col[_idx]}()")
            if std_col5[_idx] == "TRUE":
                arry_call.append(
                    " " * 20 + f"single_row_data[\"one_{std_col[_idx]}_res\"] = round(one_{std_col[_idx]}, {DECIMAL_NUM})")
            else:
                arry_call.append(" " * 20 + f"single_row_data[\"one_{std_col[_idx]}_res\"] = one_{std_col[_idx]}")
        print("END:" + "#" * 100)

        str_file_path = config_file["excelobj"]["basePath"]
        _content = ''
        with open(str_file_path, 'r', encoding='utf8') as _f:
            _content = _f.read()
            _content = str(_content).replace("###AclassName###", CALSS_NAME)
            _content = str(_content).replace("###className###", str(CALSS_NAME).lower())
            _content = str(_content).replace("## METHOD ##", "\n".join(arry_method))
            _content = str(_content).replace("## CALLING ##", "\n".join(arry_call))
            print("Make file:\n" + _content)
            _f.close()

        with open(str_out_file_path, 'a', encoding='utf8') as _f:
            _f.write(_content)
            _f.write('\n')
            _f.close()
    except Exception as e:
        print(e)


def makeTabTop():
    arry_method = []
    arry_naming = []
    try:
        p_path = config_file["excelobj"]["outPath"] + f"\\{str(CALSS_NAME).upper()}\\"
        str_out_file_path = p_path + CALSS_NAME + "TabTop.py"
        if not os.path.exists(p_path):
            os.makedirs(p_path)
        print("METHOD:" + "#" * 100)
        for _idx in range(len(top_col)):
            arry_method.append(
                " " * 4 + f"# 计算 {top_col3[_idx]} \n    # Params:\n    # {top_col3[_idx]} {top_col2[_idx]}: {top_col3[_idx]}")
            arry_method.append(" " * 4 + f"def get_{top_col[_idx]}(self,):")
            arry_method.append(" " * 8 + f"# 参考计算公式")
            arry_method.append(" " * 8 + f"# {top_col4[_idx]}")
            arry_method.append(" " * 8 + f"return 0")
            arry_method.append("\n")
        print("NAMING:" + "#" * 100)
        for _idx in range(len(top_col)):
            if top_col5[_idx] == "TRUE":
                arry_naming.append(" " * 8 + f"\"{top_col[_idx]}\" : 0,")
            else:
                arry_naming.append(" " * 8 + f"\"{top_col[_idx]}\" : '',")

        print("END:" + "#" * 100)

        str_file_path = config_file["excelobj"]["topPath"]
        _content = ''
        with open(str_file_path, 'r', encoding='utf8') as _f:
            _content = _f.read()
            _content = str(_content).replace("###AclassName###", CALSS_NAME)
            _content = str(_content).replace("## METHOD ##", "\n".join(arry_method))
            _content = str(_content).replace("## NAMING ##", "\n".join(arry_naming))
            print("Make file:\n" + _content)
            _f.close()
        with open(str_out_file_path, 'a', encoding='utf8') as _f:
            _f.write(_content)
            _f.write('\n')
            _f.close()
    except Exception as e:
        print(e)


if __name__ == '__main__':
    makeTabTop()
    # makeTabCalStd()
